﻿USE [QLLVT]
go

Create proc [dbo].[sp_SelectALLChuyenXe]
as
begin
	select * from chuyenxe
end
go
                                         
create proc [dbo].[sp_SelectALLDatCho]
as
begin
	select * from DATCHO
end
go
                                         
create proc [dbo].[sp_SelectALLLichDi]	
as
begin
	select * from LichDi
end
go
                                         
Create proc [dbo].[sp_SelectALLLoaiXe]
as
begin
	select * from LOAIXE
end
go
                                     
create proc [dbo].[sp_SelectALLNhanVien]
as
begin
	select * from NHANVIEN
end
go
                                     
create proc [dbo].[sp_SelectChuyenXeByBXDen]
	@BDen nvarchar(50)
as
begin
	select * from CHUYENXE where BENXEDEN=@BDen
end
go
                                          
create proc [dbo].[sp_SelectChuyenXeByMaChuyen]
	@machuyen char(10)
as
begin
	select * from CHUYENXE where MACHUYEN=@machuyen
end
go
                                         
create proc [dbo].[sp_SelectKhachHangALL]
as 
begin
	select * from khachhang
end
go
                                                      
create proc [dbo].[sp_SelectLichDiByMaChuyen]
	@machuyen char(10)
as
begin
	select * from LichDi where MACHUYEN=@machuyen
end
go
                                                    
create proc [dbo].[sp_SelectLichDiByMaLoai]
	@maloai char(4)
as
begin
	select * from LichDi where MALOAI=@maloai
end
go
                                                 
create proc [dbo].[sp_SelectNhanVienByMaNV]
	@manv char(10)
as
begin
	select * from NHANVIEN where MANV=@manv
end
go
                                               
CREATE proc [dbo].[sp_SelectNhanVienByMaNVPASS]
	@manv char(10),@pass char(10)
as
begin
	select * from NHANVIEN where MANV=@manv and MKHAU=@pass
end
go
                                                             
Create proc [dbo].[usp_SelectALL]
as
begin
	select * from CHUYENXE
end
go
                                                 
Create proc [dbo].[usp_SelectMa]
as
begin	
	select * from CHUYENXE
end
go
                                                   
create proc [dbo].[usp_SelectSoHieuTheoMaLoai]
	@MALOAI char(10)
as
begin
	select * from XE where MALOAI=@MALOAI
end
go
                                                         
Create proc [dbo].[usp_SuaChuyenXe]
@ma char(10),
@bdi nvarchar(50),
@bden nvarchar(50),
@giodi datetime,
@gioden datetime
as
begin
	if(not exists (select MACHUYEN from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Mã chuyến không có'
		return
	end
	if(exists (select * from CHUYENXE where BENXEDEN=@bden and BENXEDI=@bdi))
	begin
		print N'Đã tồn tại chuyến đi này'
		return
	end
	update CHUYENXE set BENXEDI=@bdi, BENXEDEN=@bden, GIODI=@giodi, GIODEN=@gioden where MACHUYEN=@ma 
end
go
                                                   
CREATE Proc [dbo].[usp_suadatcho]

@ma char(10),
@ngaydi datetime,
@machuyen char(10),
@sohieu char(10),
@soluong int
as
begin 
	update DATCHO set SOLUONG=@soluong 
	where  MAKH=@ma and NGAYDI=@ngaydi and MACHUYEN=@machuyen
	and SOHIEU=@sohieu


end
go
                                                     
CREATE Proc [dbo].[usp_sualichdi1]

@ngaydi datetime,
@machuyen char(10),
@sohieu char(10)

as
begin 
	
	declare @tt int
	set @tt = (select sum(SOLUONG) from DATCHO where MACHUYEN=@machuyen and NGAYDI=@ngaydi
	and SOHIEU=@sohieu)
	update LICHDI set TTHIENTAI=@tt where MACHUYEN=@machuyen and NGAYDI=@ngaydi
	and SOHIEU=@sohieu
end
go
                                               
Create Proc [dbo].[usp_themchuyenxe]
@ma char(10),
@bdi nvarchar(50),
@bden nvarchar(50),
@giodi datetime,
@gioden datetime
as
begin 
	if(exists (select MACHUYEN from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Mã chuyến đã có'
		return
	end
	if(exists (select * from CHUYENXE where BENXEDEN=@bden and BENXEDI=@bdi))
	begin
		print N'Đã tồn tại chuyến đi này'
		return
	end
	insert into CHUYENXE values(@ma,@bdi,@bden,@giodi,@gioden)
	print N'Thêm Thành Công'
end
go
                                                 
CREATE Proc [dbo].[usp_themdatcho]
@ma char(10),
@ngaydi datetime,
@machuyen char(10),
@sohieu char(10),
@soluong int
as
begin
	insert into DATCHO values (@ma,@Ngaydi,@machuyen,@sohieu,@soluong,1)
	
end
go 
                                                                    
CREATE Proc [dbo].[usp_themdatcho1]
@ma char(10),
@ngaydi datetime,
@machuyen char(10),
@sohieu char(10),
@soluong int
as
begin
	insert into DATCHO values (@ma,@Ngaydi,@machuyen,@sohieu,@soluong,1)
end
go
                                                                            
Create proc [dbo].[usp_ThemLichDi]
@ngayDi Datetime,
@maChuyen char(10),
@soHieu char(10),
@maLoai char(4),
@soTai int,
@soLo int,
@tthienTai int,
@tinhTrang char(5)
as
	begin try
		INSERT INTO LICHDI VALUES(@ngayDi,@maChuyen,@soHieu,@maLoai,@soTai,@soLo,@tthienTai,@tinhTrang)
	end try
	begin catch
		declare @error varchar(1000)
		set @error = N'Thêm thất bại'
		raiserror (@error,16,1)
		rollback tran
		return
	end catch
go
                                                                           
Create proc [dbo].[usp_ThemLoaiXe]
@hangsx char(10),
@maloai char(4),
@trongtai int
as
begin
	if(exists (select * from LOAIXE where MALOAI=@maloai))
	begin
		print 'Loai xe da ton tai'
		return
	end
	insert into LOAIXE values (@hangsx,@maloai,@trongtai)
end
go
                                          
create proc [dbo].[usp_xoachuyenxe]
@ma char(10)
as
begin
	if(not exists (select * from CHUYENXE where MACHUYEN=@ma))
	begin
		print N'Chuyến xe không tồn tại'
		return
	end
	delete DATCHO where MACHUYEN=@ma
	delete DANGKY where MACHUYEN=@ma
	delete LICHDI where MACHUYEN=@ma
	delete CHUYENXE where MACHUYEN=@ma
	print N'Xóa Thành Công'

end
go
                                                           
CREATE Proc [dbo].[usp_xoadatcho]

@ma char(10),
@ngaydi datetime,
@machuyen char(10),
@sohieu char(10),
@soluong int
as
begin
	declare @tt int,@ttt int
	set @tt =(select sum(SOLUONG) from DATCHO where NGAYDI=@ngaydi and MACHUYEN=@machuyen
	and SOHIEU=@sohieu)
	set @ttt=@tt-@soluong
	delete DATCHO where  MAKH=@ma and NGAYDI=@ngaydi and MACHUYEN=@machuyen
	and SOHIEU=@sohieu
	update LICHDI set TTHIENTAI=@ttt where NGAYDI=@ngaydi and MACHUYEN=@machuyen
	and SOHIEU=@sohieu


end
go
                              
CREATE proc [dbo].[usp_XoaLichDi]
@ngayDi datetime,
@maChuyen char(10),
@soHieu char(10)
as
	begin 
		DELETE FROM DangKy Where NGAYDI=@ngayDi and MACHUYEN=@maChuyen and SOHIEU=@soHieu
		DELETE FROM DATCHO Where NGAYDI=@ngayDi and MACHUYEN=@maChuyen and SOHIEU=@soHieu
		DELETE FROM LichDi Where NGAYDI=@ngayDi and MACHUYEN=@maChuyen and SOHIEU=@soHieu
	end 
go
                                     
CREATE proc [dbo].[usp_capnhattrongtailichdi]
@ngaydi datetime,
@machuyen char(10),
@sohieu char(10)
as
begin 
	declare @tt int
	set @tt =(select sum(SOLUONG) from DATCHO 
	where NGAYDI=@ngaydi and MACHUYEN=@machuyen
	and SOHIEU=@sohieu)
	update LICHDI set TTHIENTAI=@tt where NGAYDI=@ngaydi and MACHUYEN=@machuyen
	and SOHIEU=@sohieu

end
go                                        
                                  
create proc DR_T1
@maNV char(10),
@ngayDi Datetime,
@maChuyen char(10),
@soHieu char(10)
as
begin tran
begin try		
		INSERT INTO DANGKY VALUES(@maNV,@ngayDi,@maChuyen,@soHieu)
		waitfor delay '00:00:10'	

		if(EXISTS(SELECT * From NhanVien Where MANV = @maNV AND LoaiNV = '1'))
		begin
			if((SELECT COUNT(*) From DangKy dk,NhanVien nv Where dk.MaNV = nv.MaNV AND nv.LoaiNV = '1' AND NgayDi = @ngayDi AND MaChuyen=@maChuyen AND  SoHieu=@soHieu) >  (SELECT SoTai From LichDi Where NgayDi = @ngayDi AND MaChuyen=@maChuyen AND  SoHieu=@soHieu))
			begin
				print N'Đã đủ số tài'
				rollback tran
				return
			end
		end

		if(EXISTS(SELECT * From NhanVien Where MANV = @maNV AND LoaiNV = '0'))
		begin
			if((SELECT COUNT(*) From DangKy dk,NhanVien nv Where dk.MaNV = nv.MaNV AND nv.LoaiNV = '0' AND NgayDi = @ngayDi AND MaChuyen=@maChuyen AND  SoHieu=@soHieu) >  (SELECT SoLo From LichDi Where NgayDi = @ngayDi AND MaChuyen=@maChuyen AND  SoHieu=@soHieu))
			begin
				print N'Đã đủ số lơ'
				rollback tran
				return
		   end
		end

	commit tran
	print N'Đăng ký thành công'
		
end try
begin catch
	declare @error varchar(1000)
	set @error = N'Đăng ký thất bại'
	raiserror (@error,16,1)
	rollback tran
	return
end catch
go		
